drop table if exists jms_dws.dws_four_to_one_data_summary;
CREATE TABLE jms_dws.dws_four_to_one_data_summary(
   sum_date               date   comment'汇总日期'
  ,oper_network_code      varchar(60) comment'操作网点      '
  ,oper_agent_code        varchar(60) comment'代理区编码'
  ,config_type            varchar(60) comment'配置类型      '
  ,config_network_code    varchar(60) comment'配置网点编码  '
  ,oper_scan_user_name    varchar(60) comment'操作扫描员名称'
  ,oper_scan_user_code    varchar(60) comment'操作扫描员编号'
  ,oper_agent_name        varchar(60) comment'代理区名称'
  ,oper_network_name      varchar(60) comment'操作网点名称  '
  ,real_scan_type         varchar(60) comment'实际扫描类型  '
  ,config_scan_type       varchar(60) comment'生成扫描类型  '
  ,config_network_name    varchar(60) comment'配置网点名称  '
  ,config_user_name       varchar(60) comment'配置业务员名称'
  ,config_user_code       varchar(60) comment'配置业务员code'
  ,bill_cnt               int    comment'数据单量      '
)
ENGINE=olap
DUPLICATE KEY(sum_date,oper_network_code,oper_agent_code,config_type)
COMMENT "四合一数据汇总"
PARTITION BY RANGE (sum_date) (
    START ("2022-07-01") END ("2022-07-31") EVERY (INTERVAL 1 day)
)
DISTRIBUTED BY HASH(oper_network_code) BUCKETS 10
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-180",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "10",
"in_memory" = "false",
"storage_format" = "V2"
);